﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using INTEX.DataBase.Tools;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{    
    public partial class JLTAB50LSData : BaseFunction
    {
        ApplicationBase app
        {
            get
            {
                return ApplicationBase.Instance;
            }
        }
        INTEXDBC INTEXDBC
        {
            get
            {
                return app.INTEXDBC;
            }
        }
        /*public INTEXBL INTEXBL
        {
            get
            {
                return app.INTEXBL;
            }
        }

        SETINTEXDB INTEXDB = new SETINTEXDB();
         */
        public JLTAB50LSData()
            : base()
        {
        }
         public JLTAB50LSData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetAB50LS(DataRow dwTABEL)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "AB50LS";
            string TYPE = dwTABEL["TYPE"].ToString().Trim();
            string CO = dwTABEL["CO"].ToString().Trim();
            string ARTICLE = dwTABEL["ARTICLE"].ToString().Trim();
            string STATUS = dwTABEL["STATUS"].ToString().Trim();
            try
            {
                orlReadCommand = INTEXDBC.OracleDB.CreateCommand();
                orlReadCommand.CommandText = " SELECT  DISTINCT ZUSPPART AS SPPART,ZUEINTKG*0.001 AS ZUEINTKG,ZUENTNKG*0.001 AS ZUENTNKG,TRIM(B.ABKKDAUFTNR) AS CO,A.ABPAUFKRS,A.ABPAUFLFD,A.ABPAUFPOS,A.ABPART, "
                    + " TRIM(C.GSHANDLNR) AS ARTDESC,A.ABPFARBE,TRIM(D.GSCBEZ) AS COLOR, "
                    + " A.ABPSOLLMENGE5*0.01 AS ORDQTY,A.ABPZUGKG*0.01 AS ALLOQTY,(A.ABPSOLLMENGE5-A.ABPZUGKG)*0.001 AS REMQTY, "
                    + " A.ABPEAKG * 0.01 AS ISSUEQTY,A.ABPNEUDAT,A.ABPNEUBEN, "
                    + " DECODE(A.ABPERLKZ,'J','Y',(CASE WHEN A.ABPSOLLMENGE5 = A.ABPZUGKG THEN 'Y' ELSE 'N' END)) AS STATUS ,TRIM(LSNAME2) AS LSNAME2  "
                    + " FROM ABPOS A,ABKOPF B,GSDAT C,GSCOLOR D,ZUTEI K ,GPDAT O,LIEFDAT P "
                    + " WHERE A.ABPAUFKRS = '" + TYPE + "' "
                    + " AND A.ABPMANDANT = B.ABKMANDANT AND A.ABPAUFKRS = B.ABKAUFKRS "
                    + " AND A.ABPAUFKRS=K.ZUAUFKRS AND A.ABPAUFLFD=K.ZUAUFLFD AND A.ABPAUFPOS=K.ZUAUFPOS  AND ZULB LIKE 'Y*%'"
                    + " AND A.ABPAUFLFD = B.ABKAUFLFD AND A.ABPMANDANT = C.GSMANDANT "
                    + " AND A.ABPART = C.GSART AND A.ABPVAR = C.GSVAR  "
                    + " AND A.ABPMANDANT = D.GSCMANDANT AND A.ABPART = D.GSCART "
                    + " AND A.ABPVAR = D.GSCVAR AND A.ABPFARBE = D.GSCFARBE "
                    + " AND O.GPPARTIE=K.ZUPARTIE AND P.LSLIEFNR=O.GPLIEFNR"
                    + " AND TRIM(B.ABKKDAUFTNR) LIKE '" + CO + "%' "
                    + " AND TRIM(A.ABPART) LIKE '" + ARTICLE + "%' "
                    + " AND (DECODE(A.ABPERLKZ,'J','Y',(CASE WHEN A.ABPSOLLMENGE5 = A.ABPZUGKG THEN 'Y' ELSE 'N' END)) = '" + STATUS + "' "
                    + "  or '" + STATUS + "'= 'Y') "
                    + " ORDER BY ABPAUFKRS,ABPAUFLFD,ABPAUFPOS, CO,ABPFARBE,ABPART,SPPART "                   
                    ;
                
                Result = INTEXDBC.Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["ARTDESC"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["ARTDESC"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["COLOR"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["COLOR"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["CO"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["CO"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["ABPART"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["ABPART"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["SPPART"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["SPPART"] = value;
                       
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet GetBOM(DataRow dwTABLE)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "BOM";
            string CO = dwTABLE["CO"].ToString().Trim();
            try
            {
                orlReadCommand = INTEXDBC.OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT 'J' AS TYP,BAGART,BAGFARBE,BAGGART,BAGGFARBE,BAGBEM FROM BASTGA "
                    + " WHERE TRIM(BAGART)=SUBSTR('"+CO+"',1,7) "
                    + " UNION ALL "
                    + " SELECT 'W' AS TYP,WAGART,WAGWCOL,WAGGART,WAGGFARBE,WAGBEM FROM WASTGA "
                    + " WHERE TRIM(WAGART) = SUBSTR('"+CO+"',1,7) "
                    + " ORDER BY 2,3,1,5 "
                    ;
                Result = INTEXDBC.Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["BAGBEM"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["BAGBEM"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["BAGGART"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["BAGGART"] = value;
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet BuildData(DataRow dwUI)
        {
            /*DataSet SResult = new DataSet();
            SETINTEXDB SReturn = new SETINTEXDB(); */
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetAB50LS(dwUI);
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }

    }
}